<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('memory_limit', '128M');
######## EDIT THESE PATHS###########

$gffFile = '/home/anup/web/test/data/dd.gff';
$sqlDatabase = '/srv/http/anup/test/data/database.db';
$attributesSeparator = ';';
$keyValueSeparator = '=';
######## DONE WITH EDIT###########

if($_POST['submit']) {
    $db = new SQLite3($sqlDatabase);
    echo '<pre>';
    $sql = "create table if not exists gff  (
    seqid varchar(60),
    source varchar(60),
    feature varchar(30),
    start int,
    end int,
    score float,
    strand chr(1),
    phase tinyint,
    attributes text,
    genename varchar(60),
    geneID varchar(60),
    PRIMARY KEY(seqid,feature,start,end)
    );";
  
    echo $sql . "\n";
    $db->exec($sql); 
    $sql = "delete from gff";
    echo $sql . "\n";
    $db->exec($sql); 

    $gff = fopen($gffFile,'r');
    $columns_names = array(':seqid', ':source',
        ':feature',':start',':end',':score',':strand',':phase',':attributes');
    
    $sql = $db->prepare("INSERT OR IGNORE INTO 'gff' VALUES (" . join(',',$columns_names) . ")");   
    $db->exec("BEGIN");
    
    #foreach($gff as $kk =>$l) {
    while (!feof($gff)) {
        $l = fgets($gff);
        if(preg_match('/^#/',$l)) continue;
        $l = trim($l);
        $columns = preg_split ('/\t/',$l);
        if (count($columns) != 9) continue;
        if(preg_match('/"(.*)"/',$columns[8],$m))
            $columns[8] = $m[1];
        #print_r($columns);    
        foreach($columns as $k => $c) {
            $sql->bindValue($columns_names[$k], $c, SQLITE3_TEXT);
        }
        $sql->execute();
    }
    
    $db->exec("COMMIT");
    fclose($gff);
    print "Done loading GFF.\n";
    print '</pre>';
    print "Summary of Results:<br>";
    
    $sql = "select feature, count(*) n  from gff group by feature";
    $results = $db->query($sql);
    echo '<table border="1">';
    while ($row = $results->fetchArray(SQLITE3_ASSOC)) {
        print '<tr><td>' . $row['feature'] . '</td><td>' . $row['n'] . '</td></tr>';
    }
    print '</table>';
} else {
    $check['gff file exists'] = file_exists($gffFile);
    $check['gff file is readable'] = is_readable($gffFile);
    $check['database Directory is writable'] = is_writable(dirname($sqlDatabase));
    $check['sqlite3 is loaded'] = extension_loaded(SQLite3);
    $OK = True;
    echo '<table border="1">';
    foreach($check as $k=>$c) {
        if (!$c) $OK = False;
        
        if($c) $c = 'TRUE';  else $c = 'FALSE';
        print '<tr><td>' . $k . '</td><td>' . $c . '</td></tr>';
    }
    echo '</table>';
    if($OK) {
        echo '<form action="createDB.php" method="POST"><input type="submit" value="submit" name="submit"></form>';
    } else {
        echo 'There is an error. Please check the above messsages. They must all be true.';
    }
    

}
?>